<?php

/*

	Copyright © 2011 Mike Bennett <mike@kereru.com>

	This file is part of Tazu-Time.

    Tazu-Time is free software: you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation, either version 3 of the License, or
    any later version.

    Tazu-Time is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU General Public License for more details.

    You should have received a copy of the GNU General Public License
    along with Tazu-Time.  If not, see <http://www.gnu.org/licenses/>.
    
*/



//$e = get_loaded_extensions();
//array_dump($e);

class Customers extends Db {

	
	function __construct($data_file=FALSE) {
		parent::__construct($data_file);
	}

	
	/* Check database structure */
	function database_check() {

		if ( ! file_exists( $this->db_path.$this->db_file.$this->db_ext ) ) {

			$db_ver = '1.4';
			
			
			try {
				
				$db = new PDO($this->dsn);
	
				// status
				$qry = "CREATE TABLE status(
					id INTEGER PRIMARY KEY AUTOINCREMENT,
				    param  TEXT,
				    value TEXT
				)";
								
				$db->exec($qry);
	
	
				// customers
				$qry = "CREATE TABLE customers(
					id INTEGER PRIMARY KEY AUTOINCREMENT,
				    name  TEXT,
				    company TEXT,
				    email TEXT
				)";
								
				$db->exec($qry);
	
	
				// tasks
				$qry = "CREATE TABLE tasks(
					id INTEGER PRIMARY KEY AUTOINCREMENT,
				    customer_id  INTEGER,
				    task_code  TEXT,
				    task TEXT,
				    estimate_time INTEGER DEFAULT NULL,
					estimate_warning INTEGER DEFAULT NULL,
					charged INTEGER DEFAULT 0,
					period INTEGER DEFAULT 0,
					rate FLOAT DEFAULT 0
				)";
								
				$db->exec($qry);
	


	
				$qry = sprintf("INSERT INTO status (param,value) VALUES ('db_ver','%s');",$db_ver);
				$db->exec($qry);


				$db = null;
				
			} catch (PDOException $e) {
			   $this->error($e->getMessage());
				return;
			}

		}
		

		// Current database version

		$this->db_ver = $this->getDBVersion();

		if ( $this->db_ver == 1.4 ) {

			// Upgrade 1.4 -> 1.5
			// Add a billing date for one-off charges like quotes
			$db_ver = '1.5';

			try {
			
				$db = new PDO($this->dsn);
	
				$qry = "ALTER TABLE tasks ADD COLUMN bill_date INTEGER DEFAULT NULL";
				$db->exec($qry);

				$qry = sprintf("UPDATE status set value='%s' where param='db_ver'",$db_ver);
				$db->exec($qry);

				$this->db_ver = $db_ver;
	
				$db = null;
				
	
			} catch (PDOException $e) {
			   $this->error($e->getMessage());
				return;
			}


		}



	}
	
	// --- Projects / Tasks

	// Return list of all tasks for a customer


	function getProjectList($customer_id) {
		$qry = sprintf("SELECT * FROM tasks WHERE customer_id='%s' order by task_code;",$customer_id);
		return( $this->rows($qry,FALSE));
	}


	// id --> code
	function getProjectCode($id) {
		return( $this->col($id,'tasks','task_code') );
	}

	function getProjectCustId($id) {
		return( $this->col($id,'tasks','customer_id') );
	}



	// code --> id
	function getProjectId($code) {

		$col = 'id';

		try {
		
			$db = new PDO($this->cust_dsn);

			$qry = sprintf("SELECT * FROM %s WHERE task_code='%s';",'tasks',$code);
			$result = array();

		   foreach ($db->query($qry, PDO::FETCH_ASSOC) as $row) {
				$result = $row[$col];
		   }

			$db = null;
			return( $result );			

		} catch (PDOException $e) {
		   $this->error($e->getMessage());
			return;
		}

	}


	
	function getProjectDescription($id) {
		return( $this->col($id,'tasks','task') );
	}

	function getProjectField($id,$fld='task') {
		return( $this->col($id,'tasks',$fld) );
	}


	function updateTask($id,$col,$val) {
		$qry = sprintf("UPDATE tasks SET %s='%s' WHERE id='%s';",$col,$val,$id);
		return( $this->execute($qry));		
	}

	function updateProjectCode($id,$val) {
		return( $this->updateTask($id,'task_code',$val) );
	}

	function updateProjectDescription($id,$val) {
		return( $this->updateTask($id,'task',$val) );
	}


	function addProject($customer_id, $code,$description='',$estimate_time=0,$estimate_warning=0,$charged=0,$rate=0,$period=0) {
		$qry = sprintf("INSERT INTO tasks (customer_id,task_code,task,estimate_time,estimate_warning,charged,rate,period) VALUES ('%s','%s','%s','%s','%s','%s','%s','%s');",$customer_id,$code,$description,$estimate_time,$estimate_warning,$charged,$rate,$period);
		return( $this->insert($qry) );
	}

	function delProject($task_id) {
		$qry = sprintf("DELETE FROM tasks WHERE id='%s'",$task_id);
		return( $this->execute($qry));		
	}


	function updateCustomer($id,$col,$val) {
		$qry = sprintf("UPDATE customers SET %s='%s' WHERE id='%s';",$col,$val,$id);
		return( $this->execute($qry));		
	}

	function updateCustomerName($id,$val) {
		return( $this->updateCustomer($id,'name',$val) );
	}

	function updateCompanyName($id,$val) {
		return( $this->updateCustomer($id,'company',$val) );
	}

	function addCustomer($name,$company='',$email_bill='') {
		$qry = sprintf("INSERT INTO customers (name,company,email) VALUES ('%s','%s','%s');",$name,$company,$email_bill);
		return( $this->insert($qry) );
	}

	function delCustomer($customer_id) {
		$qry = sprintf("DELETE FROM customers WHERE id='%s';",$customer_id);
		return( $this->execute($qry) );
	}

	// Return list of all customers
	function getCustomers() {
		$qry = sprintf("SELECT * FROM customers;");
		return( $this->rows($qry,FALSE));
	}
	
	function getCustomerName($id) {
		return( $this->col($id,'customers','name') );
	}

	function getCustomerCompany($id) {
		return( $this->col($id,'customers','company') );
	}

	function getCustomerEmailBill($id) {
		return( $this->col($id,'customers','email') );
	}



}

?>